import requests
from bs4 import BeautifulSoup
import pymysql  # 引用库
import parsel

print('连接到mysql服务器……')
db = pymysql.connect(host='localhost',
                     port=3306,
                     user='root',
                     passwd='123456',
                     db='python',
                     charset='utf8')
# host 主机名，port 端口，user 数据库用户名，password 数据库root用户的密码，db 数据库名
print("连接上了！")

# 创建游标（远程操作mysql）
cursor = db.cursor()

cursor.execute("DROP TABLE IF EXISTS nba_player")

sql = """CREATE TABLE `nba_player` (
              `id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
              `ranks` int DEFAULT NULL COMMENT '排名',
              `player` varchar(255) DEFAULT NULL COMMENT '球员名称',
              `team` varchar(255) DEFAULT NULL COMMENT '球队',
              `score` float DEFAULT NULL COMMENT '得分',
              `hit_shot` varchar(255) DEFAULT NULL COMMENT '命中出手',
              `hit_rate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '命中率',
              `hit_three` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '命中-三分',
              `three_rate` varchar(255) DEFAULT NULL COMMENT '三分命中率',
              `hit_penalty` varchar(255) DEFAULT NULL COMMENT '命中-罚球',
              `penalty_rate` varchar(255) DEFAULT NULL COMMENT '罚球命中率',
              `session` int DEFAULT NULL COMMENT '场次',
              `playing_time` float DEFAULT NULL COMMENT '上场时间',
              PRIMARY KEY (`id`)
            )
        """
cursor.execute(sql)

# 正确的url地址 分析网页（伪装）
headers = {
    'User-Agent':
        'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Safari/537.36'
}
# 爬取的链接
url = "https://nba.hupu.com/stats/players"

# 2.发送网络请求， request(css/re/requests)
response = requests.get(url=url, headers=headers)

response.encoding = "utf-8"

html_data = response.text
# 3.数据解析（进行数据的筛选）
#  3.1转换数据类型
selector = parsel.Selector(html_data)

trs = selector.xpath('//tbody/tr[not(@class="color_font1 bg_a")]')
i = 1
for tr in trs:
    id = i
    i += 1
    rank = tr.xpath('./td[1]/text()').get()  # 排名
    player = tr.xpath('./td[2]/a/text()').get()  # 球员
    team = tr.xpath('./td[3]/a/text()').get()  # 球队
    score = tr.xpath('./td[4]/text()').get()  # 得分
    hit_shot = tr.xpath('./td[5]/text()').get()  # 命中-出手
    hit_rate = tr.xpath('./td[6]/text()').get() # 命中率
    hit_three = tr.xpath('./td[7]/text()').get()  # 命中-三分
    three_rate = tr.xpath('./td[8]/text()').get()  # 三分命中率
    hit_penalty = tr.xpath('./td[9]/text()').get()  # 命中-罚球
    penalty_rate = tr.xpath('./td[10]/text()').get()  # 罚球命中率
    session = tr.xpath('./td[11]/text()').get()  # 场次
    playing_time = tr.xpath('./td[12]/text()').get()  # 上场时间
    insert = ("INSERT INTO `nba_player` VALUES (%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")
    data = (
        id, rank, player, team, score, hit_shot, hit_rate, hit_three, three_rate, hit_penalty, penalty_rate, session,
        playing_time)
    print(data)
    cursor.execute(insert, data)
    db.commit()
    print(rank, player, team, score, hit_shot, hit_rate, hit_three,
          three_rate, hit_penalty, penalty_rate, session, playing_time)

del i

print("爬取数据完成，存入数据库……")

cursor.close()
